CREATE           procedure [dbo].[amsp_CMNavMenuSetup]

@WebsiteKey uniqueidentifier = null,
@NavContentGroupInd char(1) = 'N', @Runtime bit = 0

AS
BEGIN

/*
** MODIFICATION HISTORY
**
**    01/02/2001  N.Malhotra  Changed c_ChildNav to use #NavMenu instead of Nav_Menu
**    01/04/2002  E.Tatsui  Added logic for MicrositeFlag
**    01/22/2002  N.Malhotra  Added logic to keep level 1's dynamic
**    01/28/2002  N.Malhotra  Added @InFuseNavMenuID - If provided, the sp is being called to
**                      calculate the lowest CategoryDepth value where a difference exists
**                      between the current and previous publish operation for the same level 1
**    02/14/2002    N.Malhotra  Changed logic on how PrefuseURL is generated for content with a single link
**    02/23/2002  N.Malhotra  Added indexes to #NavMenu for performance
**    03/04/2002  N.Malhotra  Fixed problem with single link content records where .HTM files were being
**                      displayed using AMTemplate
**    03/06/2002  N.Malhotra  Deprecated retrieval of app vars to pre 4.1 versions only
**    04/09/2002  N.Malhotra  Added @App_RootPath or @App_SecurePath to beginning of all PostFuseURL values
**    04/11/2002  N.Malhotra  Changed base SQL statement from PublishedDateTime < CURRENT_TIMESTAMP to
**                      PublishedDateTime <= CURRENT_TIMESTAMP
**    04/12/2002    E.Pfleckl Changed variable and column names for compatibility with case-sensitive DBs
**    10/22/2002    C.Jewell    Changed PreFuseURL for content consisting of a single file to coincide with
**                          Source folder consolidation.
**    01/03/2003    C.Jewell  Added logic for Members Only content to be displayed outside of the template
**    02/13/2003        R.Wenger        Rewrote logic that figures out what content needs to be regenerated, if InNavMenuID is passed.
**                                      It used to just return the category depth below which all content needs to be
**                                      regenerated (see Nitin's note 1/28/2002 above).  Now it returns a result set of all
**                                      nav items that need regeneration. The ended up doing too many
**    03/28/2003        E.Tatsui        Changed so that amsp_SetNavProperties is not run when the sp is run to get modified contents.
**    05/12/2003        E.Tatsui        Changed so that http:// is not appended when URL includes [TemplatePath]
**    05/28/2003        E.Tatsui        Replaced [TemplatePath] solution with /TemplateRedirect.cfm
**    06/18/2003        E.Tatsui        Removed input parameters and simplified the procedure.
**                                      - PostFuseURLs and PreFuseURLS are now stored in the Database.
**                                      - Logic to look for modified nav menus are moved to amsp_CMGetNavMenuToRegenerate
**                                      Limit the result to Navigation items.
**    09/12/2003         R.Wenger     Added @WebsiteKey parameter and code that filters the result set by the websitekey, for use in CM6
**                                                    Added @Runtime parameter and code that filters the result set by whether hidden things need to be included
**                                                   (they don't at runtime, just at design time)
**    10/12/2003       E.Tatsui        Removed a call to amsp_CMSetNavProperties. AncestoryList is stored in Nav_Menu table now.
**    06/09/2006    R.Wenger    Added  COLLATE database_default NULL to the alter table line on the temp
**                    table #Nav_Menu because if the default collation of the system (i.e., in the temp DB
**                    is different that that of this db, the alter will use the temp db's collation.                                
*/

  DECLARE
    @AncestoryHideFlag    char(1),
    @AncestoryNotPublishedFlag  char(1),
    @TemplatePath   varchar(255),
    @CategoryDepth    integer,
    @Name     varchar(255),
    @AncestoryList    varchar(255),
    @ChildNavMenuID        numeric,
    @NumChildren           numeric,
    @ContentID      numeric,
    @NavMenuID      numeric,
    @OldNavMenuID   numeric,
    @ParentNavMenuID    numeric,
    @OldPreFuseURL    varchar(255),
    @PreFuseURL     varchar(255),
    @PostFuseURL    varchar(255),
    @URLParamChar   char(1),
    @GECodePath    varchar(255),
    @AdminWebsiteKey varchar(50),
    @VirtualDirectoryPath varchar(255)

  /*
  ** Gets the currrent, published Nav_Menu into a temp table for use by the template.
  ** This script reads the info into the table, and adds a few columns that it calculates
  ** to make the template's job a lot easier.
  */

  SELECT a.*,
         e.ImageInitial,
         e.ImageOther,
         e.ImageRollover,
         e.BreadCrumb,
         e.ImageWidth,
         e.ImageHeight,
         b.HideFlag AS ParentHideFlag,
         b.PublishedDateTime AS ParentPubValue,
         b.Name AS ParentName,
         c.ShowInTemplateFlag,
         c.OpenInNewWindowFlag,
         c.SecureFlag AS ContentSecureFlag,
         a.HideFlag AS AncestoryHideFlag,
         'N' AS AncestoryNotPublishedFlag,
         w.WebsiteRootURL,
         w.SecureWebsiteRootURL,
         w.GraphicsDirectory,
         w.TemplateHeaderFileName,
         w.TemplateFooterFileName,
         w.SiteStyleSheet,
         w.UseHierMenuFlag,
         w.ShowBreadCrumbFlag,
     w.UseAspNetTemplateFlag
    INTO #NavMenu
    FROM ((((Nav_Menu a WITH (NOLOCK)
         LEFT OUTER JOIN Nav_Menu b WITH (NOLOCK) ON a.ParentNavMenuID = b.NavMenuID)
         LEFT OUTER JOIN Content c WITH (NOLOCK) ON a.ContentID = c.ContentID AND c.WorkflowStatusCode = 'P'
          AND c.PublishDateTime <= CURRENT_TIMESTAMP AND (c.ExpirationDate >= CURRENT_TIMESTAMP
           OR c.ArchiveAtExpirationFlag IS NULL OR c.ArchiveAtExpirationFlag = 'N'))
         LEFT OUTER JOIN Content_HTML d WITH (NOLOCK)  ON c.ContentID = d.ContentID)
         LEFT OUTER JOIN Nav_Menu_Feature e WITH (NOLOCK) ON a.NavMenuID = e.NavMenuID),
         Website w
   WHERE ((a.NavContentGroupInd = 'N' AND a.PublishedDateTime <= CURRENT_TIMESTAMP)
      OR a.NavContentGroupInd = 'C')
     AND (a.MicrositeFlag = 'N' OR a.MicrositeFlag IS NULL)
     AND a.WebsiteKey = w.WebsiteKey
     AND a.NavContentGroupInd = @NavContentGroupInd
   ORDER BY a.NavContentGroupInd DESC, a.SortOrder

  ALTER TABLE #NavMenu
  ALTER Column PostFuseURL varchar(500) COLLATE database_default NULL

  -- For admin template, delete any products that are not licensed.
  SELECT @AdminWebsiteKey = Value
    FROM System_Variable WITH (NOLOCK)
   WHERE Name = 'CMAdminWebsiteKey'

   DELETE FROM #NavMenu
   WHERE WebsiteKey = @AdminWebsiteKey
     AND ComponentCode IS NOT NULL
     AND ComponentCode NOT IN (SELECT ComponentCode FROM Component_Ref WITH (NOLOCK) WHERE ActiveFlag = 'Y')

  /*
  ** Performance Boost
  */

  CREATE INDEX #IDX1 on #NavMenu(NavMenuID)
  CREATE INDEX #IDX2 on #NavMenu(ParentNavMenuID)

/*
  ** Loop through Nav_Menu determine the URL each nav menu item points to.
  */
  
  DECLARE c_Nav CURSOR FOR
    SELECT Name,
           ContentID,
           NavMenuID,
           ParentNavMenuID,
           PreFuseURL,
           PostFuseURL
      FROM #NavMenu
     WHERE IsNull(HideFlag,'N') = 'N'
     ORDER BY SortOrder
  
  OPEN c_Nav

  FETCH NEXT FROM c_Nav INTO
    @Name,
    @ContentID,
    @NavMenuID,
    @ParentNavMenuID,
    @PreFuseURL,
    @PostFuseURL
    
  SET @OldPreFuseURL = 'NOT INITIALIZED'

  WHILE (@@FETCH_STATUS=0)
  BEGIN


    /*
    ** also, as long as we are looping through here, determine if the menu item has
    ** a place to link to.  If not, determine if he has a descendant.  If so, copy
    ** the link info from his first descendant. But don't do it for the last item in
    ** the query, it has no children.
    */

    IF (CHARINDEX('MissingInclude',@OldPreFuseURL) > 0 AND
        @ParentNavMenuID = @OldNavMenuID AND
        CHARINDEX('MissingInclude',@PreFuseURL) =0)
    BEGIN

      UPDATE #NavMenu
         SET PreFuseURL = @PreFuseURL,
             PostFuseURL = @PostFuseURL
       WHERE NavMenuID = @OldNavMenuID
    END

    SET @OldPreFuseURL = @PreFuseURL
    SET @OldNavMenuID = @NavMenuID

    FETCH NEXT FROM c_Nav
      INTO
      @Name,
      @ContentID,
      @NavMenuID,

      @ParentNavMenuID,
      @PreFuseURL,
      @PostFuseURL
    
  END /* End While */
    
  
  CLOSE c_Nav
  DEALLOCATE c_Nav

  /*
  ** For items with Level = 1, the PostFuse should be dynamic (i.e. use section)
  ** UNLESS this is a single Link in which case, PostFuse should equal PreFuse.
  ** 04/09/2002 - We want to always nclude @App_RootPath or @App_SecurePath
  */

  SELECT @GECodePath = Value
    FROM System_Variable
   WHERE Name = 'GECodePath'

  SELECT @VirtualDirectoryPath = Value
    FROM System_Variable
   WHERE Name = 'VirtualDirectoryPath'

  IF Len(@GECodePath) > 1
    SET @GECodePath = RIGHT(@GECodePath,Len(@GECodePath)-1)
  ELSE IF @GECodePath = '/'
    SET @GECodePath = ''

  IF Len(@VirtualDirectoryPath) > 1
    SET @VirtualDirectoryPath = RIGHT(@VirtualDirectoryPath,Len(@VirtualDirectoryPath)-1)
  ELSE IF @VirtualDirectoryPath = '/'
    SET @VirtualDirectoryPath = ''

  UPDATE #NavMenu
     SET PostFuseURL = CASE
        WHEN IsNull(ShowInTemplateFlag,'Y') = 'Y' AND ContentSecureFlag = 'Y' THEN
          a.SecureWebsiteRootURL + @GECodePath + 'Template.cfm' + '?Section=' + Name
        WHEN IsNull(ShowInTemplateFlag,'Y') = 'Y' AND IsNULL(ContentSecureFlag, 'N') = 'N' THEN
          a.WebsiteRootURL + @GECodePath + 'Template.cfm' + '?Section=' + Name
        WHEN IsNULL(ShowInTemplateFlag, 'Y') = 'N' AND CHARINDEX('.CFM',UPPER(PreFuseURL)) > 0  AND ContentSecureFlag = 'Y' THEN
          a.SecureWebsiteRootURL + @GECodePath + 'AMTemplate.cfm?Section=' + Name
        WHEN IsNULL(ShowInTemplateFlag, 'Y') = 'N' AND CHARINDEX('.CFM',UPPER(PreFuseURL)) > 0  AND IsNULL(ContentSecureFlag, 'N') = 'N' THEN
          a.WebsiteRootURL + @GECodePath + 'AMTemplate.cfm?Section=' + Name
        ELSE
          a.PreFuseURL
      END
    FROM #NavMenu a, Website b
   WHERE CategoryDepth = 1
     AND PostFuseURL != PreFuseURL
     AND a.WebsiteKey = b.WebsiteKey
     AND b.UseAspNetTemplateFlag = 'N'

  /* Added for ASP.NET Templates)*/
  UPDATE #NavMenu
     SET PostFuseURL = CASE
        WHEN IsNull(ShowInTemplateFlag,'Y') = 'Y' AND ContentSecureFlag = 'Y' THEN
          a.SecureWebsiteRootURL + @VirtualDirectoryPath + '/' + Name + '/AM/ContentManagerNet/Default.aspx' + '?Section=' + Name
        WHEN IsNull(ShowInTemplateFlag,'Y') = 'Y' AND IsNULL(ContentSecureFlag, 'N') = 'N' THEN
          a.WebsiteRootURL + @VirtualDirectoryPath + '/' + Name + '/AM/ContentManagerNet/Default.aspx' + '?Section=' + Name
        WHEN IsNULL(ShowInTemplateFlag, 'Y') = 'N' AND CHARINDEX('.ASPX',UPPER(PreFuseURL) ) > 0  AND ContentSecureFlag = 'Y' THEN
          a.SecureWebsiteRootURL + @VirtualDirectoryPath + '/' + Name + '/AM/ContentManagerNet/Default.aspx?NoTemplate=1&Section=' + Name
        WHEN IsNULL(ShowInTemplateFlag, 'Y') = 'N' AND CHARINDEX('.ASPX',UPPER(PreFuseURL) ) > 0  AND IsNULL(ContentSecureFlag, 'N') = 'N' THEN
          a.WebsiteRootURL + @VirtualDirectoryPath + '/' + Name + '/AM/ContentManagerNet/Default.aspx?NoTemplate=1&Section=' + Name
        ELSE
          a.PreFuseURL
      END
    FROM #NavMenu a, Website b
   WHERE CategoryDepth = 1
     AND PostFuseURL != PreFuseURL
     AND a.WebsiteKey = b.WebsiteKey
     AND b.UseAspNetTemplateFlag = 'Y'

  /* Unless it's a link outside of this website, add root path or secure root to the URL. */

  UPDATE #NavMenu
     SET PreFuseURL = CASE
                        WHEN ContentSecureFlag = 'Y' THEN SecureWebsiteRootURL
                        ELSE WebsiteRootURL END +
                      CASE WHEN Left(PreFuseURL,1) = '/' THEN Right(PreFuseURL,Len(PreFuseURL)-1)
                        ELSE PreFuseURL END
   WHERE PreFuseURL NOT LIKE 'htt%'

  UPDATE #NavMenu
     SET PostFuseURL =  CASE
                        WHEN ContentSecureFlag = 'Y' THEN SecureWebsiteRootURL
                        ELSE WebsiteRootURL END +
                        CASE WHEN Left(PostFuseURL,1) = '/' THEN Right(PostFuseURL,Len(PostFuseURL)-1)
                        ELSE PostFuseURL END
   WHERE PostFuseURL NOT LIKE 'http%'
  

  /* Set AncestoryHideFlag and AncestoryNotPublishedFlag*/
  UPDATE #NavMenu
     SET AncestoryHideFlag = 'Y',
         AncestoryNotPublishedFlag = CASE WHEN b.PublishedDateTime > CURRENT_TIMESTAMP
                                               OR b.PublishedDateTime IS NULL THEN 'Y'
                                     ELSE 'N' END
    FROM #NavMenu a, Nav_Menu b WITH (NOLOCK)
   WHERE a.AncestorNavMenuID = b.NavMenuID
     AND b.HideFlag = 'Y'

  /* Added 09/12/2003 - R. Wenger
     Removes nav items that you can't see at runtime */
  IF @Runtime = 1
      DELETE FROM #NavMenu
      WHERE AncestoryHideFlag = 'Y'
              OR AncestoryNotPublishedFlag = 'Y'
              OR HideFlag = 'Y'
              OR PublishedFlag = 'N'

  /* Return the entire #NavMenu table if not filtering for CM6 */
  IF @WebsiteKey IS NULL
     SELECT * from #NavMenu
     ORDER BY NavContentGroupInd DESC, SortOrder
  /* Added 09/12/2002 - R.Wenger
     otherwise, CM6 is per website, and we don't need all the crap you can't show anyway */
  ELSE
     SELECT *
       FROM #NavMenu
    WHERE WebsiteKey = @WebsiteKey
     ORDER BY NavContentGroupInd DESC, SortOrder
END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMNavMenuSetup] TO [IMIS]
GO